//
//  DBHelper.m
//  sweetbox
//
//  Created by apollo on 15/9/15.
//  Copyright (c) 2015年 proj-m. All rights reserved.
//

#import "DBHelper.h"
#import "log.h"

@interface DBHelper()
@property (nonatomic, strong) NSString *dbPath;
@property (nonatomic, readonly) BOOL isDictDBOpen;
@property (nonatomic, readonly) BOOL isPresDBOpen;
@end

@implementation DBHelper


+ (DBHelper *)sharedDBHelper {
    static dispatch_once_t pred = 0;
    __strong static DBHelper *_sharedObject = nil;
    dispatch_once(&pred, ^{
        _sharedObject = [[self alloc] initWithNil];
        
    });
    return _sharedObject;
}

- (void) closeDictDB {
    _isDictDBOpen = NO;
    [_dictDB close];
}

- (DBHelper *) initWithNil {
    if (self = [super init]) {
        _dictDB = nil;
        _presDB = nil;
        _isDictDBOpen = NO;
        
        NSArray *docPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *docPath = [docPaths objectAtIndex:0];
        self.dbPath = [NSString stringWithFormat:@"%s/db", [docPath cStringUsingEncoding:NSUTF8StringEncoding]];
        BOOL isDir = YES;
        NSFileManager *fileManager = [NSFileManager defaultManager];
        BOOL existed = [fileManager fileExistsAtPath:self.dbPath isDirectory:&isDir];
        if (NO == existed) {
            [fileManager createDirectoryAtPath:self.dbPath withIntermediateDirectories:YES attributes:nil error:nil];
        }
            
    }
    return self;
}

- (BOOL) createDictDB:(NSString *)platform openID:(NSString *)openID {
    NSDate *today = [NSDate date];
    NSDateFormatter *todayFmt = [NSDateFormatter new];
    [todayFmt setDateFormat:@"yyyyMMdd"];
    NSString *todayStr = [todayFmt stringFromDate:today];
    
    
    NSString *dictDBName = [NSString stringWithFormat:@"dict_%d_%d_%d_%s_%s_%s.db", 0, 0, 1,[platform cStringUsingEncoding:NSUTF8StringEncoding], [openID cStringUsingEncoding:NSUTF8StringEncoding],[todayStr cStringUsingEncoding:NSUTF8StringEncoding]];
    NSString *dictDBPath = [NSString stringWithFormat:@"%s/%s",[self.dbPath cStringUsingEncoding:NSUTF8StringEncoding], [dictDBName cStringUsingEncoding:NSUTF8StringEncoding]];
    FMDatabase *db = [FMDatabase databaseWithPath:dictDBPath];
    if (![db open]) {
        LOG_E("Open db %s Error",[dictDBPath cStringUsingEncoding:NSUTF8StringEncoding]);
        return NO;
    } else {
        LOG_E("Open db %s Success",[dictDBPath cStringUsingEncoding:NSUTF8StringEncoding]);
    }
    _isDictDBOpen = YES;
    
    NSString *sqlCMD = @"CREATE TABLE IF NOT EXISTS `dictionary` (word VARCHAR PRIMARY KEY, translate TEXT);";
    BOOL ret = [db executeStatements:sqlCMD];
    if (NO == ret) {
        LOG_E("Create Table dictionary error!");
        return NO;
    }
    _dictDB = db;
    
    NSUserDefaults *sud = [NSUserDefaults standardUserDefaults];
    NSString *dbKey = [NSString stringWithFormat:@"%s_%s_dict_db_path", [platform cStringUsingEncoding:NSUTF8StringEncoding], [openID cStringUsingEncoding:NSUTF8StringEncoding]];
    [sud setValue:dictDBName forKey:dbKey];
    return  YES;
}

- (BOOL) openDictDB:(NSString *)platform openID:(NSString *)openID {
    if (YES == _isDictDBOpen) {
        return YES;
    }
    
    NSUserDefaults *sud = [NSUserDefaults standardUserDefaults];
    NSString *dbKey = [NSString stringWithFormat:@"%s_%s_dict_db_path", [platform cStringUsingEncoding:NSUTF8StringEncoding], [openID cStringUsingEncoding:NSUTF8StringEncoding]];

    NSString *dictDBName = [sud valueForKey:dbKey];
    NSString *dictDBPath = [NSString stringWithFormat:@"%s/%s",[self.dbPath cStringUsingEncoding:NSUTF8StringEncoding], [dictDBName cStringUsingEncoding:NSUTF8StringEncoding]];
    FMDatabase *db = [FMDatabase databaseWithPath:dictDBPath];
    if (![db open]) {
        LOG_E("Open db %s Error",[dictDBPath cStringUsingEncoding:NSUTF8StringEncoding]);
        return NO;
    }
    LOG_I("Open db %s Success",[dictDBPath cStringUsingEncoding:NSUTF8StringEncoding]);
    _dictDB = db;
    _isDictDBOpen = YES;
    return YES;
}

- (BOOL) insertWord:(NSString *)word translate:(NSString *)translate {
    if (YES != self.isDictDBOpen) {
        return NO;
    }
    if (nil == word || [word isEqualToString:@""]) {
        LOG_E("insertWord with bad word");
        return NO;
    }
    
    if (nil == translate || [translate isEqualToString:@""]) {
        LOG_E("insertWord with bad translate");
        return NO;
    }
    
    NSString *sqlCMD = @"INSERT INTO dictionary VALUES (?, ?)";
    BOOL ret = [self.dictDB executeUpdate:sqlCMD, word, translate];
    
    return ret;
}

- (BOOL) updateWord:(NSString *)word translate:(NSString *)translate {
    if (YES != self.isDictDBOpen) {
        return NO;
    }
    if (nil == word || [word isEqualToString:@""]) {
        LOG_E("updateWord with bad word");
        return NO;
    }
    
    if (nil == translate || [translate isEqualToString:@""]) {
        LOG_E("updateWord with bad translate");
        return NO;
    }
    
    NSString *sqlCMD = @"UPDATE dictionary SET translate=? WHERE word=?";
    BOOL ret = [self.dictDB executeUpdate:sqlCMD, translate, word];
    
    return ret;
}

- (int) countWrods {
    if (YES != self.isDictDBOpen) {
        return 0;
    }
    int totalCount = 0;
    FMResultSet *s = [self.dictDB executeQuery:@"SELECT COUNT(*) FROM dictionary"];
    if ([s next]) {
        totalCount = [s intForColumnIndex:0];
    }
    return totalCount;
}

- (NSArray *) wordAt:(int) index {
    if (YES != self.isDictDBOpen) {
        return nil;
    }
    int i=0;
    
    FMResultSet *s = [self.dictDB executeQuery:@"SELECT * FROM dictionary"];
    while ([s next]) {
        if (i++ == index) {
            NSString *word = [s stringForColumnIndex:0];
            NSString *translate = [s stringForColumnIndex:1];
            return @[word, translate];
        }
    }
    return nil;
}

- (BOOL) deleteWord:(NSString *) word{
    if (YES != self.isDictDBOpen) {
        return NO;
    }
    if (nil == word || [word isEqualToString:@""]) {
        LOG_E("deleteWord with bad word");
        return NO;
    }
    BOOL ret = [self.dictDB executeUpdate:@"DELETE  FROM dictionary WHERE word=?", word];
    return ret;
}

- (NSArray *) wordAtKey:(NSString *)key {
    FMResultSet *s = [self.dictDB executeQuery:@"SELECT * FROM dictionary WHERE word=?",key];
    while ([s next]) {
            NSString *word = [s stringForColumnIndex:0];
            NSString *translate = [s stringForColumnIndex:1];
            return @[word, translate];

    }
    return nil;
}

- (BOOL) createPresDB:(NSString *)platform openID:(NSString *)openID {
    NSDate *today = [NSDate date];
    NSDateFormatter *todayFmt = [NSDateFormatter new];
    [todayFmt setDateFormat:@"yyyyMMdd"];
    NSString *todayStr = [todayFmt stringFromDate:today];
    
    
    NSString *presDBName = [NSString stringWithFormat:@"pres_%d_%d_%d_%s_%s_%s.db", 0, 0, 1,[platform cStringUsingEncoding:NSUTF8StringEncoding], [openID cStringUsingEncoding:NSUTF8StringEncoding],[todayStr cStringUsingEncoding:NSUTF8StringEncoding]];
    NSString *presDBPath = [NSString stringWithFormat:@"%s/%s",[self.dbPath cStringUsingEncoding:NSUTF8StringEncoding], [presDBName cStringUsingEncoding:NSUTF8StringEncoding]];
    FMDatabase *db = [FMDatabase databaseWithPath:presDBPath];
    if (![db open]) {
        LOG_E("Open db %s Error",[presDBPath cStringUsingEncoding:NSUTF8StringEncoding]);
        return NO;
    } else {
        LOG_E("Open db %s Success",[presDBPath cStringUsingEncoding:NSUTF8StringEncoding]);
    }
    _isPresDBOpen = YES;
    
    NSString *sqlCMD = @"CREATE TABLE IF NOT EXISTS `prescription_books` ( name VARCHAR PRIMARY KEY, brief TEXT);";
    BOOL ret = [db executeStatements:sqlCMD];
    if (NO == ret) {
        LOG_E("Create Table prescription_books error!");
        return NO;
    }
    
    sqlCMD = @"CREATE TABLE IF NOT EXISTS `prescription_scripts` (book_name  VARCHAR ,title VARCHAR , script TEXT, PRIMARY KEY(book_name, title));";
    ret = [db executeStatements:sqlCMD];
    if (NO == ret) {
        LOG_E("Create Table prescription_scripts error!");
        return NO;
    }
    
    _presDB = db;
    
    NSUserDefaults *sud = [NSUserDefaults standardUserDefaults];
    NSString *dbKey = [NSString stringWithFormat:@"%s_%s_pres_db_path", [platform cStringUsingEncoding:NSUTF8StringEncoding], [openID cStringUsingEncoding:NSUTF8StringEncoding]];
    [sud setValue:presDBName forKey:dbKey];
    return YES;
}

- (BOOL) openPresDB:(NSString *)platform openID:(NSString *)openID {
    if (YES == _isPresDBOpen) {
        return YES;
    }
    
    NSUserDefaults *sud = [NSUserDefaults standardUserDefaults];
    NSString *dbKey = [NSString stringWithFormat:@"%s_%s_pres_db_path", [platform cStringUsingEncoding:NSUTF8StringEncoding], [openID cStringUsingEncoding:NSUTF8StringEncoding]];
    
    NSString *presDBName = [sud valueForKey:dbKey];
    NSString *presDBPath = [NSString stringWithFormat:@"%s/%s",[self.dbPath cStringUsingEncoding:NSUTF8StringEncoding], [presDBName cStringUsingEncoding:NSUTF8StringEncoding]];
    FMDatabase *db = [FMDatabase databaseWithPath:presDBPath];
    if (![db open]) {
        LOG_E("Open db %s Error",[presDBPath cStringUsingEncoding:NSUTF8StringEncoding]);
        return NO;
    }
    LOG_I("Open db %s Success",[presDBPath cStringUsingEncoding:NSUTF8StringEncoding]);
    _presDB = db;
    _isPresDBOpen = YES;
    return YES;
}

- (void) closePresDB {
    _isPresDBOpen = NO;
    [_presDB close];
}

- (BOOL) insertBook:(NSString *)name brief:(NSString *)brief {
    if (YES != self.isPresDBOpen) {
        return NO;
    }
    if (nil == name || [name isEqualToString:@""]) {
        LOG_E("insertBook with bad book");
        return NO;
    }
    
    if (nil == brief || [brief isEqualToString:@""]) {
        LOG_E("insertBook with bad brief");
        return NO;
    }
    
    NSString *sqlCMD = @"INSERT INTO prescription_books VALUES (?, ?)";
    BOOL ret = [self.presDB executeUpdate:sqlCMD, name, brief];
    
    return ret;
}

- (BOOL) updateBook:(NSString *)name brief:(NSString *)brief {
    if (YES != self.isPresDBOpen) {
        return NO;
    }
    if (nil == name || [name isEqualToString:@""]) {
        LOG_E("insertBook with bad book");
        return NO;
    }
    
    if (nil == brief || [brief isEqualToString:@""]) {
        LOG_E("insertBook with bad brief");
        return NO;
    }
    NSString *sqlCMD = @"UPDATE prescription_books SET brief=? WHERE name=?";
    BOOL ret = [self.presDB executeUpdate:sqlCMD, name, brief];
    return ret;
}

- (int) countBooks {
    if (YES != self.isPresDBOpen) {
        return 0;
    }
    
    int totalCount = 0;
    FMResultSet *s = [self.presDB executeQuery:@"SELECT COUNT(*) FROM prescription_books"];
    if ([s next]) {
        totalCount = [s intForColumnIndex:0];
    }
    return totalCount;

}

- (NSArray *) bookAt:(int) index {
    if (YES != self.isPresDBOpen) {
        return nil;
    }
    int i=0;
    
    FMResultSet *s = [self.presDB executeQuery:@"SELECT * FROM prescription_books"];
    while ([s next]) {
        if (i++ == index) {
            NSString *name = [s stringForColumnIndex:0];
            NSString *brief = [s stringForColumnIndex:1];
            return @[name, brief];
        }
    }
    return nil;
}

- (NSArray *) bookAtName:(NSString *)name {
    if (YES != self.isPresDBOpen) {
        return nil;
    }
    
    if (nil == name || [name isEqualToString:@""]) {
        LOG_E("bookAtName with bad name");
        return nil;
    }
    
    FMResultSet *s = [self.presDB executeQuery:@"SELECT * FROM prescription_books WHERE name=?",name];
    while ([s next]) {
        NSString *name = [s stringForColumnIndex:0];
        NSString *brief = [s stringForColumnIndex:1];
        return @[name, brief];
        
    }
    return nil;
}

- (BOOL) deleteBook:(NSString *) name {
    if (YES != self.isPresDBOpen) {
        return NO;
    }
    
    if (nil == name || [name isEqualToString:@""]) {
        LOG_E("deleteBook with bad name");
        return NO;
    }
    BOOL ret = [self.presDB executeUpdate:@"DELETE  FROM prescription_books WHERE name=?", name];
    return ret;
}

- (BOOL) insertScript:(NSString *)bookName title:(NSString *)title script:(NSString *)script {
    if (YES != self.isPresDBOpen) {
        return NO;
    }
    if (nil == title || [title isEqualToString:@""]) {
        LOG_E("insertScript with bad title");
        return NO;
    }
    if (nil == bookName || [bookName isEqualToString:@""]) {
        LOG_E("insertScript with bad bookName");
        return NO;
    }
    
    if (nil == script || [script isEqualToString:@""]) {
        LOG_E("insertScript with bad script");
        return NO;
    }
    
    NSString *sqlCMD = @"INSERT INTO prescription_scripts VALUES (?, ?, ?)";
    BOOL ret = [self.presDB executeUpdate:sqlCMD, bookName, title, script];
    
    return ret;

}

- (BOOL) updateScript:(NSString *)bookName title:(NSString *)title script:(NSString *)script {
    if (YES != self.isPresDBOpen) {
        return NO;
    }
    if (nil == title || [title isEqualToString:@""]) {
        LOG_E("insertScript with bad title");
        return NO;
    }
    if (nil == bookName || [bookName isEqualToString:@""]) {
        LOG_E("insertScript with bad bookName");
        return NO;
    }
    
    if (nil == script || [script isEqualToString:@""]) {
        LOG_E("insertScript with bad script");
        return NO;
    }
    NSString *sqlCMD = @"UPDATE prescription_scripts SET script=? WHERE book_name=? AND title=?";
    BOOL ret = [self.presDB executeUpdate:sqlCMD, script, bookName, title];
    return ret;
    
}

- (int) countScript:(NSString *)bookName {
    if (YES != self.isPresDBOpen) {
        return 0;
    }
    
    if (nil == bookName || [bookName isEqualToString:@""]) {
        LOG_E("countScript with bad bookName");
        return NO;
    }
    int totalCount = 0;
    FMResultSet *s = [self.presDB executeQuery:@"SELECT COUNT(*) FROM prescription_scripts"];
    if ([s next]) {
        totalCount = [s intForColumnIndex:0];
    }
    return totalCount;
}

- (NSArray *) scriptAt:(NSString *)bookName index:(int) index {
    if (YES != self.isPresDBOpen) {
        return nil;
    }
    
    if (nil == bookName || [bookName isEqualToString:@""]) {
        LOG_E("scriptAt with bad bookName");
        return nil;
    }
    
    int i=0;
    
    FMResultSet *s = [self.presDB executeQuery:@"SELECT * FROM prescription_scripts"];
    while ([s next]) {
        if (i++ == index) {
            NSString *bookName = [s stringForColumnIndex:0];
            NSString *title = [s stringForColumnIndex:1];
            NSString *script = [s stringForColumnIndex:2];
            return @[bookName, title, script];
        }
    }
    return nil;
}


- (NSArray *) scriptAtTitle:(NSString *)bookName title:(NSString *)title {
    if (YES != self.isPresDBOpen) {
        return nil;
    }
    
    if (nil == bookName || [bookName isEqualToString:@""]) {
        LOG_E("scriptAt with bad bookName");
        return nil;
    }
    
    if (nil == title || [title isEqualToString:@""]) {
        LOG_E("scriptAt with bad title");
        return nil;
    }
    
    FMResultSet *s = [self.presDB executeQuery:@"SELECT * FROM prescription_scripts WHERE book_name=? AND title=?", bookName, title];
    while ([s next]) {
        NSString *bookName = [s stringForColumnIndex:0];
        NSString *title = [s stringForColumnIndex:1];
        NSString *script = [s stringForColumnIndex:2];
        return @[bookName, title, script];
    }
    return nil;
}
- (BOOL) deleteScript:(NSString *)bookName title:(NSString *) title {
    if (YES != self.isPresDBOpen) {
        return NO;
    }
    
    if (nil == bookName || [bookName isEqualToString:@""]) {
        LOG_E("scriptAt with bad bookName");
        return NO;
    }
    
    if (nil == title || [title isEqualToString:@""]) {
        LOG_E("scriptAt with bad title");
        return NO;
    }
    BOOL ret = [self.presDB executeUpdate:@"DELETE  FROM prescription_scripts WHERE book_name=? AND title=?", bookName, title];
    return ret;
}
@end
